package com.fantasy.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**  
 * @author fantasy <br>
 * E-mail: vi2014@qq.com
 * @version 2015年6月2日 上午10:31:14
 **/
public class ReadExcel {
	
	public static Workbook getWorkbook(String path) {
		File f = new File(path);
		return getWorkbook(f);
	}
	
	public static Workbook getWorkbook(File xlsFile) {
		InputStream rptXlsInput = null;

		Workbook wb = null;
		try {
			rptXlsInput = new FileInputStream(xlsFile);
			wb = new HSSFWorkbook(rptXlsInput);
			rptXlsInput.close();
		} catch (OfficeXmlFileException | IOException e) {
			//System.out.println("aaaaa");
			try {
				wb = new XSSFWorkbook(xlsFile);
			} catch (IOException | InvalidFormatException e1) {
				System.out.println(xlsFile + "不是一个有效的Excel文件");
			}
		}
		return wb;
	}
	
	 /**
     * 获取Cell类型
     * @param cell
     * @return
     */
    public static int getCellFormatType(Cell cell) {
    	if(cell != null){
    		return cell.getCellType();
    	}else{
    		return Cell.CELL_TYPE_BLANK;
    	}
    }
    
    /**
     * 根据HSSFCell类型设置数据
     * @param cell
     * @return
     */
    public static String getCellFormatValue(Cell cell) {
    	return getCellFormatValueSetPlaces(cell, -1, 0);
    }
	
	 /**
     * 根据HSSFCell类型设置数据
     * @param cell
     * @return
     */
    public static String getCellFormatValueSetPlaces(Cell cell , int places , int roundType) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
	            // 如果当前Cell的Type为NUMERIC
	            case HSSFCell.CELL_TYPE_NUMERIC:
	            case HSSFCell.CELL_TYPE_FORMULA: {
	                // 判断当前的cell是否为Date
	                if (HSSFDateUtil.isCellDateFormatted(cell)) {
	                    // 如果是Date类型则，转化为Data格式
	                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
	                    //cellvalue = cell.getDateCellValue().toLocaleString();
	                    
	                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12
	                    Date date = cell.getDateCellValue();
	                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	                    cellvalue = sdf.format(date);
	                }
	                // 如果是纯数字
	                else {
	                    // 取得当前Cell的数值
	                    cellvalue = String.valueOf(cell.getNumericCellValue());
	                    //System.out.println("数字" + cellvalue);
	                    if(cellvalue.endsWith(".0")){
	                    	cellvalue = cellvalue.replace(".0", "");
	                    }else if(places != -1){
                    		BigDecimal b = new   BigDecimal(cellvalue);
                    		cellvalue = b.setScale(places, roundType).toString();
	                    }
	                }
	                break;
	            }
	            // 如果当前Cell的Type为STRIN
	            case HSSFCell.CELL_TYPE_STRING:
	                // 取得当前的Cell字符串
	            	//System.err.println("CELL_TYPE_STRING : " + cellvalue);
	                cellvalue = cell.getRichStringCellValue().getString();
	                break;
	            // 默认的Cell值
	            default:
	                cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }
    
    public static void main(String[] args) {
    	BigDecimal   b   =   new   BigDecimal("1.55564811");  
		String aa = b.setScale(3, 4).toString();
		System.out.println(aa);
	}
}